﻿using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using System;
using System.Collections.Generic;
using System.Web.UI.WebControls;
using Microsoft.SharePoint.WebControls;
using System.Data;
using System.Data.SqlClient;
namespace referrals.Layouts.referrals
{
    public partial class NewReferralsCourtReports : LayoutsPageBase
    {
        private const string SortDirection = "DateReferred DESC";
        private const string StrClient = "SELECT Clients.NHSNumber, Clients.Title, Clients.FirstName, Clients.MiddleName, Clients.LastName, Clients.DOB, Clients.Gender, Clients.Area, Clients.Location, Contacts.ReasonReferred, Contacts.DateReferred, Contacts.ReferredBy, Contacts.Description, Contacts.Type, Contacts.ContactBy1, Contacts.Date, Contacts.Urgency, Contacts.ContactBy2, Contacts.AssessmentDate, Contacts.ReportFeedback, Contacts.ReportSent, Contacts.referralResponse, Contacts.DNW, Contacts.RefOutcome, Contacts.Closed, Clients.UID, [DOB], Contacts.RefDiscussion, Contacts.FOutcome, Contacts.PapersLocation, Contacts.CaseReference, Contacts.HearingDate, Contacts.TravelC, Contacts.BillingHoursC, Contacts.Reporttype, Contacts.OurRef, Contacts.RemandStatus, Contacts.Requiredby, Contacts.Knownto, Contacts.AddInfoReq, Contacts.Addinfodate, Contacts.ContactID, Contacts.TravelT, Contacts.BillingHoursT, Contacts.Invoicestatus FROM Clients LEFT JOIN Contacts ON Clients.UID = Contacts.ClientID WHERE (((Contacts.ReasonReferred)=5 Or (Contacts.ReasonReferred)=6) AND ((Contacts.Type)=62) AND ((Contacts.Closed)='False'));";

        protected void Page_Load(object sender, EventArgs e)
        {
            
            //string strReferrals = "SELECT Contacts.ClientID, Contacts.ReasonReferred, Contacts.DateReferred, Contacts.ReferredBy, Contacts.Description, Contacts.Urgency, Contacts.Type, Contacts.DNW, Contacts.ContactBy1, Contacts.ContactBy2, Contacts.AssessmentDate, Contacts.ReportFeedback, Contacts.ReportSent, Contacts.referralResponse, Contacts.DNW, Contacts.RefOutcome, Contacts.Closed, Contacts.RefDiscussion, Contacts.RefDiscussion, Contacts.FOutcome, Contacts.PapersLocation FROM Contacts WHERE (((Contacts.ClientID)='" + Request.QueryString["uid"] + "')) ORDER BY Contacts.DateReferred DESC;";
            using (SqlConnection conn = new SqlConnection(Connection.GetConnStrSql()))
            {
                //SqlConnection conn = connection.GetConnection();
                SqlCommand sqlCommand = new SqlCommand(StrClient, conn);
                conn.Open();

                SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                DataSet ds = new DataSet();
                da.Fill(ds);

                //grdReferralDetails.RowDataBound += new GridViewRowEventHandler(GridView1_RowDataBound);

                using (ds)
                {
                    grdDetails.DataSource = ds;
                    grdDetails.DataBind();
                }

                ViewState["SortExpr"] = SortDirection;
                DataView dvContacts = Getdata();
                grdDetails.DataSource = dvContacts;
                grdDetails.DataBind();
            }
        }

        protected string GetAge(DateTime birthday)
        {
            int years = new DateTime(DateTime.Now.Subtract(birthday).Ticks).Year - 1;
            return String.Format("Age: {0} Year(s)", years);
        }

        protected void grdDetails_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                Label lblGender = (e.Row.FindControl("lblGender") as Label);
                Table tblHeader = (e.Row.FindControl("tblHeader") as Table);
                if (lblGender != null)
                {
                    if (lblGender.Text == "Female")
                    {
                        if (tblHeader != null)
                        {
                            tblHeader.BackColor = System.Drawing.Color.PaleVioletRed;
                            tblHeader.ForeColor = System.Drawing.Color.White;
                        }
                    }
                    else
                    {
                        if (tblHeader != null)
                        {
                            tblHeader.BackColor = System.Drawing.Color.Blue;
                            tblHeader.ForeColor = System.Drawing.Color.White;
                        }
                    }
                }
            }

        }

        protected void grdDetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            grdDetails.PageIndex = e.NewPageIndex;
            DataView dvNewAdmitRefs = Getdata();
            grdDetails.DataSource = dvNewAdmitRefs;
            grdDetails.DataBind();
        }

        private DataView Getdata()
        {
            using (SqlConnection conn = new SqlConnection(Connection.GetConnStrSql()))
            {
                DataSet dsContactDetails = new DataSet();
                const string strSelectCmd = StrClient;
                SqlDataAdapter da = new SqlDataAdapter(strSelectCmd, conn);
                da.Fill(dsContactDetails, "Contacts");
                DataView dvEmp = dsContactDetails.Tables["Contacts"].DefaultView;
                dvEmp.Sort = ViewState["SortExpr"].ToString();
                return dvEmp;
            }
        }
    }
}
